# all the imports
import sqlite3,os,re
from json import loads,dumps
from flask import Flask, request, session, g, redirect, url_for, \
     abort, render_template, flash, make_response
from querybuilder import *
from pprint import pprint

# configuration
DATABASE = '201201.sqlite'
semester = 201201;

# create our little application :)
app = Flask(__name__)
app.config.from_object(__name__)
app.secret_key = '\xac\xf7+\xb52\xebsn\x84R\xf8J\xdb\xe4h\x02\x13j@v\xcc\x82\xdd\xa3U\xb3\xf8\x96'

def connect_db():
  return sqlite3.connect(DATABASE)

@app.before_request
def before_request():
  g.db = connect_db()
  
def fetch_dict(c):
  columns = [x[0] for x in c.description]
  for row in c.fetchall():
    yield dict(zip(columns, row))

@app.teardown_request
def after_request(response):
  g.db.close()
  return response

def escape_sql(s):
  return re.sub(r'[^a-zA-Z -_0-9]+', '', s)
  
@app.route('/')
def index():
  return render_template('index.html')

# @app.route('/program-maps')
@app.route("/program-maps")
def program_maps():
  semester = request.args.get("semester")
  if not semester:
    return dumps(dict(error="Missing semester parameter"))
  
  c = g.db.cursor()
  c.execute("""
  select distinct progdesc, dept, program, semester
  from Conflict
  where semester = ?
  order by dept
  """, (semester,))
  result = list(fetch_dict(c))
  return dumps(dict(response=result, error=0))
  
@app.route("/program-map")
def program_map():
  semester = request.args.get("semester")
  program = request.args.get("program")
  # if not (semester and program):
    # return dumps(dict(error="Missing semester parameter"))
  cur = g.db.execute("""
  select distinct code, coursedesc, category, catdesc
  from Conflict
  where semester=? and program=?
  order by code
  """, (semester, program))
  result = list(fetch_dict(cur))
  return dumps(dict(response=result, error=0))

@app.route('/courses')
def courses():
  keyword = request.args.get("keyword")
  if not semester:
    return dumps(dict(error="Missing semester parameter"))
  cur = g.db.cursor()
  if not keyword:
    cur.execute("""
    select code, title
    from Course
    order by code
    """)
  else:
    cur.execute("""
    select code, title
    from Course
    where (code like '%%%(keyword)s%%' or title like '%%%(keyword)s%%')
    """ % dict(keyword=escape_sql(keyword)))

  result = list(fetch_dict(cur))
  return dumps(dict(response=result, error=0))


  
@app.route('/schedule')
def page_schedule():
  courses = request.args.getlist('course')
  more = request.args.get('more-courses', "").upper()
  courses.extend(
    (x if x.endswith('u') else x+'u'
    ) for x in re.findall(r'\w{4}\s*\d{4}u?', more))
  return render_template("schedule.html",  courses=courses)
  
@app.route("/q")
def get_events():
  query = request.args.get("search", "").upper().strip()
  spaces = re.sub(r'\s', '',query);
  if query.startswith("FREE"):
    evtype = "free" 
  elif spaces == '':
    evtype = "clear"
  else:
    evtype = "sched"
  if query.startswith("FREE"):
    query = query[4:].strip()
  keys = re.findall(r'\S+:', query)
  values = re.split(r'\S+:', query)
  default = values[0]
  values = [x.strip() for x in values[1:]]
  keys = [x[:-1].lower() for x in keys]
  qopts = dict(zip(keys, values))
  qopts['default'] = default
  qopts['semester'] = request.args.get('semester')
  if evtype == 'free':
    print "Calling free() with"
    pprint(qopts)
    return free(qopts)
  elif evtype == 'clear':
    return spaces
  else:
    return get_schedule(qopts)

def build_where(opts):
  where = []
  for k,v in opts.items():
    where.append("%s %s" % (k, v))
  return where

def get_schedule(opts):
  result = []
  error = 0
  semester = opts.pop('semester')
  if not semester:
    return dumps(dict(error="Missing semester parameter"))
  else:
    q_courses = set(re.findall(r"[A-Z]{4}\s*[0-9]{4}[UG]",
                           opts.pop('default').upper()))
    courses = list(escape_sql(x) for x in q_courses)
    where_courses = 'Schedule.code in (%s)' % ",".join(
                    "'%s'" % x for x in courses)
    where_others = build_where(opts)
    if courses or where_others:
      where = []
      if courses:
        where.append(where_courses)
      if where_others:
        where.extend(where_others)
      c = g.db.cursor()
      sql = """
      select *
      from Section left join Schedule using (sec, code, semester)
           join Course using (code)
      where semester=? and %s
      """ % " and ".join(where)
      print "SCHEDULE\n",sql
      c.execute(sql, (semester,))
      result = list(fetch_dict(c))
    else:
      error = "No constraints defined."

    return dumps(dict(evtype="sched", response=result, error=error))

def free(opts):
  semester = opts.pop('semester')
  dur = int(opts.get("duration", "90"))
  btime = opts.get("btime")
  day = opts.get("day")
  if btime and day:
    timeonly = False
  else:
    timeonly = True
  search = []
  # Search for btime and duration if specified
  if not timeonly:
    search.append(("btime", "=", btime))
    search.append(("day", "=", day))

  # parse the search bar
  for field, pattern in opts.items():
    try:
      if field.lower() in ['campus', 'location', 'room', 'capacity', 'size',
                           'dur', 'duration']:
        if field.lower() in ['campus']:
          search.append(('campus', 'like', "%" + escape_sql(pattern) + "%"))
        elif field.lower() in ['location', 'room']:
          search.append(('location', 'like', escape_sql(pattern) + "%"))
        elif field.lower() in ['dur', 'duration']:
          try:
            dur = int(pattern)
          except:
            pass
        elif field.lower() in ['capacity', 'size']:
          try:
            search.append(('capacity', '>=', int(pattern)))
          except:
            pass
    except:
      pass

  sql = sql_freerooms(dur, search, timeonly=timeonly)
  print "FREE:\n", sql
  c = g.db.cursor()
  c.execute(sql, (semester,))
  
  result = list(fetch_dict(c))
  return dumps(dict(evtype="free", response=result, error=0))

# builds the parametric SQL query to fetch the free rooms
# of given duration, and with the where conditions.
#SQL is parameterized by semester
#where: list( (attr, pattern) )
def sql_freerooms(dur, search=None, timeonly=False):

  n = int(math.ceil(dur / 30.))
  _from = "FreeRoom F0"
  j = 0
  for j in range(1, n):
    i = j - 1
    _from += """ join FreeRoom F%(j)d on 
                (F%(i)d.day = F%(j)d.day and
                 F%(i)d.location = F%(j)d.location and
                 F%(i)d.semester = F%(j)d.semester and
                 F%(i)d.btimemin+F%(i)d.duration = F%(j)d.btimemin) """ % dict(
                 i=i, j=j)
  _where = "where F0.semester=?"
  if search:
    for (attr, op, val) in search:
      if not type(val) == int: val = "'%s'" % val
      _where += " and F0.%s %s %s" % (attr, op, val)

  if timeonly:
    return """select F0.btime, F0.day,
              F%(j)d.btimemin+F%(j)d.duration-F0.btimemin as duration,
              count(distinct F0.location) as numlocs
              from %(_from)s %(where)s
              group by F0.btime, F0.day, duration""" % dict(
              _from=_from, j=j, where=_where)

  else:
    return """SELECT distinct F0.campus, F0.location, F0.btime, F0.day,
              F%(j)d.btimemin+F%(j)d.duration-F0.btimemin as duration
              from %(_from)s %(where)s""" % dict(_from=_from, j=j, where=_where)

    
if __name__ == '__main__':
  app.debug = True
  app.run()

